package z.t.b.db.meta.mysql;

import z.t.b.db.meta.*;
import z.t.b.db.utils.StrUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.stream.Collectors;

/**
 * xx
 *
 * @Description TODO
 * @Author ztb
 * @Date 2024/3/23 17:24
 * @Version 1.0
 */
public class MysqlMetadataLoader extends BaseMetadataLoader {
    @Override
    public List<Table> loadTablesFromDB(DB db) throws SQLException {
        String sql = "select TABLE_NAME,TABLE_TYPE,TABLE_COMMENT from information_schema.TABLES where TABLE_SCHEMA='"+db.getSchema()+"'";
        List<String> tableList = db.getTableList();
        if(tableList != null && tableList.size()>0){
            sql += " AND TABLE_NAME IN ('"+tableList.stream().collect(Collectors.joining("','"))+"') ";
        }
        try(Statement stm = db.getConn().createStatement()){
            ResultSet rs = stm.executeQuery(sql);
            while(rs.next()){
                String tableName = rs.getString(1);
                tableName = tableName.toUpperCase();
                Table table = new Table(db);
                table.setTableSchema(db.getSchema());
                table.setTableName(tableName);
                String type = rs.getString(2);
                if(type.indexOf("VIEW") != -1){
                    table.setObjectType(Table._TYPE_VIEW);
                }else if("BASE TABLE".equals(type)){
                    table.setObjectType(Table._TYPE_TABLE);
                }else{
                    table.setObjectType(type);
                }
                table.setTableComment(rs.getString(2));
                db.addTable(table);
            }
        }
        for(Table table : db.getTables().values()){
            loadColumnsByTable(table);
            loadKeysByTable(table);
            getCreateDDL(table);
        }
        return db.getTables().values().stream().collect(Collectors.toList());
    }

    @Override
    public Table loadTableFromDB(DB db, String tableName) throws SQLException {
        String sql = "select TABLE_TYPE,TABLE_COMMENT from information_schema.TABLES where TABLE_SCHEMA='"+db.getSchema()+"' and TABLE_NAME='"+tableName+"'";
        try(Statement stm = db.getConn().createStatement()){
            ResultSet rs = stm.executeQuery(sql);
            if(!rs.next()){
                return null;
            }
            Table table = new Table(db);
            table.setTableSchema(db.getSchema());
            table.setTableName(tableName);
            String type = rs.getString(1);
            if(type.indexOf("VIEW") != -1){
                table.setObjectType(Table._TYPE_VIEW);
            }else if("BASE TABLE".equals(type)){
                table.setObjectType(Table._TYPE_TABLE);
            }else{
                table.setObjectType(type);
            }
            table.setTableComment(rs.getString(2));
            return table;
        }
    }

    @Override
    public List<Column> loadColumnsByTable(Table table) throws SQLException {
        DB db = table.getOwner();
        String sql = "select UPPER(COLUMN_NAME) as COLUMN_NAME,UPPER(COLUMN_TYPE) as COLUMN_TYPE,IS_NULLABLE,COLUMN_DEFAULT,"
                + "COLUMN_COMMENT,EXTRA from information_schema.columns where TABLE_SCHEMA=" + "'" + table.getTableSchema() + "'"
                + " and "
                + "TABLE_NAME=" + "'" + table.getTableName() + "'" + " order by ORDINAL_POSITION asc";
        try(Statement stm = db.getConn().createStatement()) {
            ResultSet rs = stm.executeQuery(sql);
            while (rs.next()) {
                Column column = new Column(table);
                column.setName(rs.getString("COLUMN_NAME"));
                column.setType(rs.getString("COLUMN_TYPE"));
                column.setDefaultValue(rs.getString("COLUMN_DEFAULT"));
                column.setIsNull(rs.getString("IS_NULLABLE"));
                column.setExtra(rs.getString("EXTRA"));
                column.setComment(rs.getString("COLUMN_COMMENT"));
                table.addColumn(column);
            }
        }
        return table.getColumns().values().stream().collect(Collectors.toList());
    }

    @Override
    public List<Index> loadKeysByTable(Table table) throws SQLException {
        String sql = "show keys from " + table.getTableSchema() + "." + table.getTableName();
        try(Statement stm = table.getOwner().getConn().createStatement()){
            ResultSet rs = stm.executeQuery(sql);
            Index index = null;
            while(rs.next()){
                String keyName = rs.getString("Key_name");
                if(index == null || !keyName.equals(index.getIndexName())){
                    index = new Index(table);
                    index.setIndexName(keyName);
                    index.getColumns().add(rs.getString("Column_name"));
                    index.setNotUnique(rs.getString("Non_unique"));
                    table.addIndex(index);
                }else{
                    index.getColumns().add(rs.getString("Column_name"));
                }
            }
        }
        return table.getIndexes().values().stream().collect(Collectors.toList());
    }

    @Override
    public String getCreateDDL(Table table) throws SQLException {
        String ddl = null;
        if(Table._TYPE_TABLE.equals(table.getObjectType())){
            ddl = getTableCreateDDL(table);
        }else if(Table._TYPE_VIEW.equals(table.getObjectType())){
            ddl = getViewCreateDDL(table);
        }
        table.setCreateDDL(ddl);
        return ddl;
    }

    @Override
    public String getTableCreateDDL(Table table) throws SQLException{
        String sql = "show create table " + table.getTableSchema() + "." + table.getTableName();
        try(Statement stm = table.getOwner().getConn().createStatement()){
            ResultSet rs = stm.executeQuery(sql);
            if(rs.next()){
                String ddl = rs.getString("Create Table");
                ddl = StrUtils.clearSQuotes(ddl);
                ddl = StrUtils.clearEngine(ddl);
                ddl = StrUtils.clearCharset(ddl);

                ddl = StrUtils.clearCharacterCollate(ddl);
                ddl = StrUtils.clearCollate(ddl);
                ddl = ddl.replace("CREATE TABLE", "CREATE TABLE IF NOT EXISTS");
                return ddl;
            }
        }
        return null;
    }

    @Override
    public String getViewCreateDDL(Table table) throws SQLException{
        String sql = "show create view " + table.getTableSchema() + "." + table.getTableName();
        try(Statement stm = table.getOwner().getConn().createStatement()){
            ResultSet rs = stm.executeQuery(sql);
            if(rs.next()){
                String ddl = rs.getString("Create View");
                ddl = StrUtils.clearAlgorithm(ddl);
                ddl = StrUtils.clearDefiner(ddl);
                ddl = StrUtils.clearSqlSecurityDefiner(ddl);
                ddl = ddl.replace("CREATE", "CREATE OR REPLACE");
                return ddl;
            }
        }
        return null;
    }
}
